// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Data.Common;
using System.IO;
using System.Net;
using Kailua.net.windward.utils;
using Microsoft.Win32;
using net.windward.utils.ado.Access;
using net.windward.utils.ado.DB2;
using net.windward.utils.ado.MySql;
using net.windward.utils.ado.Oracle;
using net.windward.utils.ado.SqlServer;
using net.windward.utils.ado.Unknown;

// bugbug
// 0: get user/system/all tables/views/stored procedures
// 1: get FK:PK relationships for columns.
// 2: get correct column type for all DB types
// 2: type-safe table & column names
// 3: get schema owner for tables, views, stored procedures
// 3: unit test connecting to Oracle, DB2, MySql, & Access
// 4: get stored procedures variables (type & description)

// what's returned - http://msdn.microsoft.com/en-us/library/cc668760.aspx & http://msdn.microsoft.com/en-us/library/cc716722.aspx

namespace net.windward.utils.ado.ODBC
{
	/// <summary>
	/// This is similiar to DbProviderFactory and provides additional functionality for each provider. All properties to list
	/// if a provider can perform any task in it's child classes is provider here, not in the child classes. This
	/// class can return IWrServer objects for a given server/instance that exists on a computer. The ODBC/OleDb
	/// concept of a Provider (which is different from a DbProviderFactory) is handled as a property set in the
	/// WrProviderFactory class so all other cases do not need to implement a class for that layer.
	/// This is called WrVendor instead of WrProvider because it is different from DbProviderFactory and because
	/// we use the term Provider for the ODBC/OleDb providers.
	/// This is the class for the ODBC database.
	/// </summary>
	public class WrOdbcVendor : WrVendor
	{
		/// <summary>
		/// The DbProviderFactory class for this vendor
		/// </summary>
		public const string FactoryClass = "System.Data.Odbc";

		/// <summary>
		/// The suggested name for this vendor.
		/// </summary>
		public const string FactoryName = "ODBC";

		private string odbcProvider;

		/// <summary>
		/// Creates an ODBC vendor object.
		/// </summary>
		public WrOdbcVendor()
			: base(FactoryName, "The standard ODBC connector", FactoryClass)
		{
		}

		/// <summary>
		/// Creates an ODBC vendor object.
		/// </summary>
		/// <param name="name">The friendly name of the provider.</param>
		/// <param name="description">The description of this provider (optional).</param>
		/// <param name="providerClass">The class of the provider.</param>
		public WrOdbcVendor(string name, string description, string providerClass)
			: base(name, description, providerClass)
		{
		}

		#region Properties

		/// <summary>
		/// True if can enumerate running servers from this vendor. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateServers
		{
			get { return true; }
		}

		/// <summary>
		/// True if can enumerate databases on a server. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateDatabases
		{
			get { return false; }
		}

		/// <summary>
		/// True if can launch the administrator for this vendor. This will return true if it can find the admin
		/// program file. In that case the launch could still fail.
		/// </summary>
		public override bool CanLaunchAdministrator
		{
			get { return false; }
		}

		/// <summary>
		/// True if can run DDL scripts.
		/// </summary>
		public override bool CanRunScripts
		{
			get { return false; }
		}

		/// <summary>
		/// True if can use the WindowsIdentity of the caller eliminating the need to pass a username and password.
		/// </summary>
		public override bool CanUseTrustedConnection
		{
			get { return true; }
		}

		/// <summary>
		/// True if you need to select a provider for this vendor.
		/// </summary>
		public override bool HasProviders
		{
			get { return true; }
		}

		/// <summary>
		/// True if this vendor has the concept of databases in an installed copy of their product. This is false for Oracle (which
		/// does not have the concept of a database) and for databases like Access that are just a file - and the file is the "server."
		/// </summary>
		public override DATABASE_MODE DatabaseMode
		{
			get { return DATABASE_MODE.OPTIONAL; }
		}

		/// <summary>
		/// The name of the (ODBC/OleDb) providers for this (vendor) provider.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this (vendor) providers does not support (ODBC/OleDb) providers.</exception>
		public override string OdbcProvider
		{
			get { return odbcProvider; }
			set
			{
				odbcProvider = value;
				syntax = CalcSyntax(providerFactory, odbcProvider);
			}
		}

		/// <summary>
		/// All servers from this vendor on the network. This may not get all due to routers not passing
		/// on a broadcast or delays in some servers responding.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this provider does not support enumerate servers.</exception>
		/// <returns>All servers found on the sub-net.</returns>
		public override IWrServer[] GetServers()
		{
			return GetServers(providerFactory);
		}

		private static IWrServer[] GetServers(DbProviderFactory provider)
		{
			string[] userDSN, sysDSN;
			using (
				RegistryKey keyCLSID = Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources\\", false))
			{
				if (keyCLSID == null)
					sysDSN = new string[0];
				else
					sysDSN = keyCLSID.GetValueNames();
				Array.Sort(sysDSN);
			}
			using (RegistryKey keyCLSID = Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources\\", false)
				)
			{
				if (keyCLSID == null)
					userDSN = new string[0];
				else
					userDSN = keyCLSID.GetValueNames();
				Array.Sort(userDSN);
			}

			IWrServer[] rtn = new WrOracleServer[userDSN.Length + sysDSN.Length];
			for (int ind = 0; ind < userDSN.Length; ind++)
				rtn[ind] = new WrOracleServer(provider, userDSN[ind]);
			for (int ind = 0; ind < sysDSN.Length; ind++)
				rtn[userDSN.Length + ind] = new WrOracleServer(provider, sysDSN[ind]);
			return rtn;
		}

		///<summary>
		/// Create a syntax object if we can guess who for.
		///</summary>
		///<param name="provider">The connector provider.</param>
		///<param name="odbcProvider">The ODBC provider.</param>
		///<returns>The syntax. null if can't figure it out.</returns>
		public static IWrSyntax CalcSyntax(DbProviderFactory provider, string odbcProvider)
		{

			if (string.IsNullOrEmpty(odbcProvider))
				return new WrUnknownSyntax(provider, "");

			odbcProvider = odbcProvider.ToLower();
			if (odbcProvider.Contains("db2"))
				return new WrDB2Syntax(provider);
			if (odbcProvider.Contains("mysql"))
				return new WrMySqlSyntax(provider);
			if (odbcProvider.Contains("oracle"))
				return new WrOracleSyntax(provider);
			if (odbcProvider.Contains("sql server") || odbcProvider.Contains("sqlserver") ||
			    odbcProvider.Contains("sql native client"))
				return new WrSqlServerSyntax();
			if (odbcProvider.Contains("access"))
				return new WrAccessSyntax(provider, odbcProvider);
			return new WrUnknownSyntax(provider, odbcProvider);
		}

		#endregion

		#region WrProvider methods

		/// <summary>
		/// A connection string for a database on a server.
		/// </summary>
		/// <param name="server">The server to access (ex: localhost).</param>
		/// <param name="database">The database to connect to.</param>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="showPassword">True to include the password, false to add the password as *****.</param>
		/// <returns>The connection string for this database on this server.</returns>
		public override string ConnectionString(string server, string database, WrCredentials credentials, bool showPassword)
		{
			return ConnectionString(providerFactory, odbcProvider, server, database, credentials, showPassword);
		}

		internal static string ConnectionString(DbProviderFactory providerFactory, string odbcProvider, string server, string database, WrCredentials credentials, bool showPassword)
		{

			if (string.IsNullOrEmpty(odbcProvider) || (string.IsNullOrEmpty(server) && string.IsNullOrEmpty(database)))
				return "";

			DbConnectionStringBuilder connStrBuilder = providerFactory.CreateConnectionStringBuilder();
			string password = showPassword ? credentials.Password : "*****";

			// if server only it may be a DSN
			if (string.IsNullOrEmpty(database))
			{
				IWrServer[] DSNs = GetServers(providerFactory);
				foreach (IWrServer srvr in DSNs)
					if (srvr.Name == server)
					{
						if (!string.IsNullOrEmpty(server))
							connStrBuilder.Add("DSN", server);
						if (!credentials.UseWindowsIdentity)
						{
							if (!string.IsNullOrEmpty(credentials.Username))
								connStrBuilder.Add("Uid", credentials.Username);
							if (!string.IsNullOrEmpty(credentials.Password))
								connStrBuilder.Add("Pwd", password);
						}
						return connStrBuilder.ConnectionString;
					}
			}

			// all below need a provider
			if (!string.IsNullOrEmpty(odbcProvider))
				connStrBuilder.Add("Driver", odbcProvider);
			string lcProvider = odbcProvider.ToLower();

			// Access
			if (lcProvider.Contains("access"))
				return WrAccessVendor.ConnectionString(providerFactory, odbcProvider, server, credentials, showPassword);

			// DB2
			if (lcProvider.Contains("db2"))
			{
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("Hostname", server);
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("Database", database);
				connStrBuilder.Add("Protocol", "TCPIP");
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("Uid", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Pwd", password);
				return connStrBuilder.ConnectionString;
			}

			// Excel
			if (lcProvider.Contains("excel"))
			{
				Trap.trap();
				if (!string.IsNullOrEmpty(server))
				{
					string filename = FileUtils.FullPath(server);
					connStrBuilder.Add("Dbq", filename);
					connStrBuilder.Add("DefaultDir", Path.GetDirectoryName(filename));
				}
				connStrBuilder.Add("DriverId", "790");
				return connStrBuilder.ConnectionString;
			}

			// Firebird
			if (lcProvider.Contains("firebird"))
			{
				Trap.trap();
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("DbName", FileUtils.FullPath(server));
				if (!credentials.UseWindowsIdentity)
				{
					if (!string.IsNullOrEmpty(credentials.Username))
						connStrBuilder.Add("Uid", credentials.Username);
					if (!string.IsNullOrEmpty(credentials.Password))
						connStrBuilder.Add("Pwd", password);
				}
				return connStrBuilder.ConnectionString;
			}

			// Informix
			if (lcProvider.Contains("informix"))
			{
				Trap.trap();
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("Server", server);
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("Database", database);
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("Uid", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Pwd", password);
				return connStrBuilder.ConnectionString;
			}

			// MySql
			if (lcProvider.Contains("mysql"))
			{
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("Server", server);
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("Database", database);
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("User", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Password", password);
//what is this?				connStrBuilder.Add("Option", "3");
				return connStrBuilder.ConnectionString;
			}

			// Oracle
			if (lcProvider.Contains("oracle"))
			{
				if (!string.IsNullOrEmpty(database))
				{
					if (!string.IsNullOrEmpty(server))
						connStrBuilder.Add("Server", server);
					if (!string.IsNullOrEmpty(database))
						connStrBuilder.Add("Dbq", database);
				} 
				else
					if (!string.IsNullOrEmpty(server))
						connStrBuilder.Add("Dbq", server);
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("Uid", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Pwd", password);
				return connStrBuilder.ConnectionString;
			}

			// Postgre SQL
			if (lcProvider.Contains("postgre"))
			{
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("Server", server);
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("Database", database);
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("Uid", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Pwd", password);
				return connStrBuilder.ConnectionString;
			}

			// Progress
			if (lcProvider.Contains("progress"))
			{
				Trap.trap();
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("HOST", server);
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("DB", database);
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("UID", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("PWD", password);
				return connStrBuilder.ConnectionString;
			}

			// Sybase
			if (lcProvider.Contains("sybase"))
			{
				Trap.trap();
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("Srvr", server);
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("Database", database);
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("Uid", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Pwd", password);
				return connStrBuilder.ConnectionString;
			}

			// Sql Server - the default
			if ((string.IsNullOrEmpty(database)) && (server != null) && (server.IndexOf('.') != -1))
			{
				string filename = FileUtils.FullPath(server);
				if (File.Exists(filename))
					connStrBuilder.Add("AttachDbFilename", FileUtils.FullPath(server));
				else
					connStrBuilder.Add("Server", server);
			}
			else
			{
				if (!string.IsNullOrEmpty(server))
					connStrBuilder.Add("Server", server);
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("Database", database);
			}
			if (!string.IsNullOrEmpty(server))
			{
				IPAddress addr;
				if (IPAddress.TryParse(server, out addr))
					connStrBuilder.Add("Network Library", "TCPIP");
			}
			if (credentials.UseWindowsIdentity)
				connStrBuilder.Add("Trusted_Connection", "yes");
			else
			{
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("Uid", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Pwd", password);
			}
			return connStrBuilder.ConnectionString;
		}

		/// <summary>
		/// Returns the parameters from a connection string making the best guess. Will return null for items it could not determine.
		/// </summary>
		/// <param name="connectionString">The connection string to parse.</param>
		/// <returns>The parameters.</returns>
		public override WrConnectionParams ConnectionParams(string connectionString)
		{
			if (string.IsNullOrEmpty(connectionString))
				return new WrConnectionParams();

			DbConnectionStringBuilder builder = providerFactory.CreateConnectionStringBuilder();
			builder.ConnectionString = connectionString;
			string provider = null, server = null, database = null, username = null, password = null;
			if (builder.ContainsKey("driver"))
				provider = (string)builder["driver"];
			if (builder.ContainsKey("server"))
				server = (string) builder["server"];
			else
				if (builder.ContainsKey("hostname"))
					server = (string)builder["hostname"];
				else
					if (builder.ContainsKey("dbname"))
						server = (string)builder["dbname"];
					else
						if (builder.ContainsKey("dbq"))
							server = (string)builder["dbq"];
						else
							if (builder.ContainsKey("srvr"))
								server = (string)builder["srvr"];
			if (builder.ContainsKey("database"))
				database = (string)builder["database"];
			if (builder.ContainsKey("username"))
				username = (string)builder["username"];
			else
				if (builder.ContainsKey("uid"))
					username = (string)builder["uid"];
			if (builder.ContainsKey("password"))
				password = (string)builder["password"];
			else
				if (builder.ContainsKey("pwd"))
					password = (string)builder["pwd"];
			return new WrConnectionParams(provider, server, database, username, password);
		}

		/// <summary>
		/// Return all (ODBC/OleDb) providers for this (vendor) provider.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this (vendor) providers does not support (ODBC/OleDb) providers.</exception>
		/// <returns>The name of each provider.</returns>
		public override OdbcDriverInfo[] GetOdbcProviders()
		{

			using (RegistryKey keyCLSID = Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBCINST.INI\\ODBC Drivers\\", false))
			{
				if (keyCLSID == null)
					return new OdbcDriverInfo[0];

				string[] drivers = keyCLSID.GetValueNames();
				OdbcDriverInfo[] rtn = new OdbcDriverInfo[drivers.Length];
				for (int ind = 0; ind < drivers.Length; ind++)
				{
					rtn[ind] = new OdbcDriverInfo(drivers[ind], drivers[ind]);
					/* doesn't use this
					using (RegistryKey keyDriver = Registry.LocalMachine.OpenSubKey(
							string.Format("SOFTWARE\\ODBC\\ODBCINST.INI\\{0}\\", drivers[ind]), false))
					{
						if (keyDriver != null)
							rtn[ind] = new OdbcDriverInfo(drivers[ind], keyDriver.GetValue("Driver").ToString());
					}
					*/
				}
				Array.Sort(rtn);
				return rtn;
			}
		}

		#endregion

		#region DbProvider methods

		/// <summary>
		/// Returns a new instance of the provider's class that implements the WrCommand class.
		/// </summary>
		/// <returns>A new instance of WrCommand.</returns>
		public override WrCommand CreateCommand()
		{
			return new WrOdbcCommand(providerFactory, odbcProvider);
		}

		/// <summary>
		/// Create a server object.
		/// </summary>
		/// <param name="server">The name of the server the database is on.</param>
		public override IWrServer CreateServer(string server)
		{
			return new WrOdbcServer(providerFactory, odbcProvider, server);
		}

		#endregion

	}
}
